﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;
using System.Data.Common;

namespace YourMoney
{
    /// <summary>
    /// Основная форма приложения
    /// </summary>
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }
        DataSet set = new DataSet("MainGridTable");
        /// <summary>
        /// Заполнение treeview значениями типов сущностей
        /// </summary>
        void FillTreeView()
        {
            typeTreeView.Nodes.Clear();
            List<TreeNode> rootsList = StaticMembers.RootEssenceSelect();
            for (int i = 0; i < rootsList.Count; i++)
            {
                StaticMembers.SetEssenceTree(rootsList[i]);
                typeTreeView.Nodes.Add(rootsList[i]);

            }
            typeTreeView.ExpandAll();
        }
        public void MyExportExcel(DataGridView DGV, string FILENAME)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0;
            int k = 0;
            for (j = 0; j <= DGV.ColumnCount - 1; j++)
            {
                if (DGV.Columns[j].Visible)
                {
                    xlWorkSheet.Cells[i + 1, k + 1] = DGV.Columns[j].Name;
                    k++;
                }
            }
           // DGV.
            for (i = 0; i <= DGV.RowCount - 1; i++)
            {
                 k = 0;
                for (j = 0; j <= DGV.ColumnCount - 1; j++)
                {
                    
                    if (DGV.Columns[j].Visible)
                    {
                        DataGridViewCell cell = DGV[j, i];
                        xlWorkSheet.Cells[i + 2, k + 1] = cell.Value;                        
                        k++;
                    }
                }
              //  xlWorkSheet.Cells.AutoOutline();
            }
            try
            {
                xlWorkBook.SaveAs(FILENAME, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            }
            catch
            {
                 throw new Exception("Lol! Выберите другое имя файла");
            }
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

        }
        private void releaseObject(object obj)
        {
            try
            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
            }
        }
        private void Form1_Load(object sender, EventArgs e)
        {

            try
            {
                Tables t = new Tables();
                var drv = SQLiteDriver.GetDrv("Money.db3");
                typeTreeView.HideSelection = false;
                morePicker.Value = DateTime.Now.AddMonths(-1);
              /*  DbDataReader reader = (drv.Query("select datetime(max(date),'unixepoch') 'date' from Essence;"));
                if (reader.HasRows)
                {
                    lessPicker.Value = DateTime.Parse(reader["date"].ToString());
                }

                reader = (drv.Query("select datetime(min(date),'unixepoch') 'date' from Essence;"));
                if (reader.HasRows)
                {
                    morePicker.Value = DateTime.Parse(reader["date"].ToString());
                }*/
                lessDatecheckBox.Checked = true;
                moreDatecheckBox.Checked = true;
                KindListBoxUpdate();
                MainFormSelect();
                FillTreeView();
            }
            catch (Exception ex)
            {
                MessageBox.Show("База данных пуста, либо её схема не соответствует приложению" + ex.Message);
                this.Close();
            }
        }

        private void KindListBoxUpdate()
        {
            kindListbox.Items.Clear();
            foreach (TypeOfEssence type in StaticMembers.KindSelect())
            {
                kindListbox.Items.Add(type);
            }
            kindListbox.SetItemChecked(0, true);
            kindListbox.SetItemChecked(1, true);
        }


        /// <summary>
        /// Выборка данных из БД по значениям,заданным фильтром
        /// </summary>
        private void MainFormSelect()
        {

            set.Clear();
            dataGridView1.Refresh();
            SQLiteDriver drv = SQLiteDriver.GetDrv("Money.db3");
            SQLiteConnection conn = drv.Connection;
            SQLiteCommand commandParam = new SQLiteCommand(conn);
            commandParam.CommandText = "";
            commandParam.Parameters.Clear();
            List<string> commands = new List<string>();
            string kindCommand = string.Format("t.root_id IN ()");
            for (int i = 0; i < kindListbox.CheckedItems.Count; i++)
            {
                string paramName = string.Format("@root_id{0}", i);
                commandParam.Parameters.Add(new SQLiteParameter(paramName, ((TypeOfEssence)kindListbox.CheckedItems[i]).ID));
                if (i == 0)
                {
                    kindCommand = kindCommand.Insert(kindCommand.IndexOf("(") + 1, commandParam.Parameters[i].ParameterName);
                }
                else
                {
                    kindCommand = kindCommand.Insert(kindCommand.IndexOf("0") + 1, "," + commandParam.Parameters[i].ParameterName);
                }
            }
           // commands.Add(kindCommand);
            string sizeCommand = "";
            if (moreSumcheckBox.Checked)
            {
                sizeCommand ="(" + "e.size>@moreSizeParameter" + ")";
            }
            if ((lessSumcheckBox.Checked) && (sizeCommand != ""))
            {
                sizeCommand = sizeCommand.Insert(sizeCommand.LastIndexOf(")") + 1, "AND" + " (" + "e.size<@lessSizeParameter") + ") ";
            }
            else if ((sizeCommand=="")&&(lessSumcheckBox.Checked))
            {
                sizeCommand = "(" + "e.size<@lessSizeParameter" + ")";
            }
            if (sizeCommand != "") commands.Add(sizeCommand);
            commandParam.Parameters.Add(new SQLiteParameter("@moreSizeParameter", moreTbx.Text));
            commandParam.Parameters.Add(new SQLiteParameter("@lessSizeParameter", lessTbx.Text));

            string dateCommand = "";
            if (moreDatecheckBox.Checked) 
            {
                dateCommand = "(" + "date(e.date,'unixepoch')>=date(@moreDateParameter,'unixepoch')" + ")";
            }
            if ((lessDatecheckBox.Checked) && (dateCommand != ""))
            {
                dateCommand = dateCommand.Insert(dateCommand.LastIndexOf(")") + 1, "AND" + " (" + "date(e.date,'unixepoch')<=date(@lessDateParameter,'unixepoch')") + ") ";
            }
            else if ((dateCommand == "")&&(lessDatecheckBox.Checked))
            {
                dateCommand = "(" + "date(e.date,'unixepoch')<=date(@lessDateParameter,'unixepoch')" + ")";
            }
            commandParam.Parameters.Add(new SQLiteParameter("@lessDateParameter", (int)((lessPicker.Value - new DateTime(1970, 1, 1)).TotalSeconds)));
            commandParam.Parameters.Add(new SQLiteParameter("@moreDateParameter", (int)((morePicker.Value - new DateTime(1970, 1, 1)).TotalSeconds)));
    //        MessageBox.Show(new DateTime(1970, 1, 1).AddSeconds((int)commandParam.Parameters["@lessDateParameter"].Value).ToString());
            if (dateCommand != "") 
            {
                commands.Add(dateCommand);
            }
            string mainCommand = @"SELECT tec.name 'Вид',t.name 'Тип' ,t.color 'Цвет',e.size 'Размер', datetime(e.date, 'unixepoch') 'Дата' FROM Essence as e 
            JOIN TypeOfEssence as t  On (e.type_id = t.id) join TypeOfEssence as tec ON (t.root_id=tec.id);";
 
            //string mc = mainCommand;
            mainCommand = mainCommand.Insert(mainCommand.IndexOf(";"), " where " + kindCommand+" order by 5") ;
            foreach (string str in commands) 
            {
                mainCommand = mainCommand.Insert(mainCommand.IndexOf("order"), "AND "+"(" + str+")");
            }
            commandParam.CommandText = mainCommand;
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(commandParam);
            try
            {

                adapter.Fill(set, "MainTable");
                dataGridView1.DataMember = "MainTable";
                dataGridView1.DataSource = set;
                dataGridView1.Columns["Цвет"].Visible = false;
            }
            catch (Exception ex) 
            {
                MessageBox.Show(ex.Message);
            }
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                dataGridView1.Rows[i].DefaultCellStyle.BackColor =
                ControlPaint.Light(Color.FromArgb(Convert.ToInt32(dataGridView1.Rows[i].Cells["Цвет"].Value)),3/2);
               
            }
        }
        private void exit_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void NewIncome_Click(object sender, EventArgs e)
        {
            AddForm addForm = new AddForm();
            addForm.ShowDialog();
        }

        private void NewExpense_Click(object sender, EventArgs e)
        {
        }

        private void выходToolStripMenuItem_Click(object sender, EventArgs e)
        {
            SaveFileDialog sf = new SaveFileDialog();
            sf.Filter = "xls файлы (*.xls)|*.xls";
            sf.ShowDialog();
            MyExportExcel(dataGridView1, sf.FileName);
        }

        private void button2_Click(object sender, EventArgs e)
        {
           
            SQLiteDriver drv = SQLiteDriver.GetDrv("Money.db3");
            string cmd;
            TypeOfEssence es = ((TypeOfEssence)typeTreeView.SelectedNode.Tag);
            if (es.IsRoot == 0)
            {
                cmd = "DELETE FROM TypeOfEssence WHERE parent_id = '" + es.ParentID + "' and name = '" + es.Name + "';";
                drv.NonExecuteQuery(cmd);
                FillTreeView();
            }
            else
                MessageBox.Show("Невозможно удалить элемент!");
        }

        private void AddExpenseTypeBtn_Click(object sender, EventArgs e)
        {

        }

        private void lessDatecheckBox_CheckedChanged(object sender, EventArgs e)
        {
            lessPicker.Enabled = lessDatecheckBox.Checked;
        }

        private void lessSumcheckBox_MouseClick(object sender, MouseEventArgs e)
        {
            
        }

        private void lessSumcheckBox_CheckedChanged(object sender, EventArgs e)
        {
            lessTbx.Enabled = lessSumcheckBox.Checked;
        }

        private void moreSumcheckBox_CheckedChanged(object sender, EventArgs e)
        {
            moreTbx.Enabled = moreSumcheckBox.Checked;
        }

        private void moreDatecheckBox_CheckedChanged(object sender, EventArgs e)
        {
            morePicker.Enabled = moreDatecheckBox.Checked;
        }

        private void MainForm_Activated(object sender, EventArgs e)
        {
            FillTreeView();
        }

        private void typeTreeView_AfterSelect(object sender, TreeViewEventArgs e)
        {

        }

        private void DelNewEsseseBtn_Click(object sender, EventArgs e)
        {
            try
            {
                SQLiteDriver drv = SQLiteDriver.GetDrv("Money.db3");
                string command = "DELETE FROM 'Essence' where id=(SELECT id FROM 'Essence' WHERE size='" + Convert.ToInt32(dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[3].Value) + /*"' and date='" + dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[3].Value + */"');";
                drv.NonExecuteQuery(command);
                MainFormSelect();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Вы не выбрали строку для удаления!"+ex.Message);
            }
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
           
        }

        private void selectAllBtn_Click(object sender, EventArgs e)
        {
                MainFormSelect();
        }
        public int TypeOfDiagram = 0;
        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            TypeOfDiagram = 1;    
            DiagramForm DiagForm = new DiagramForm(TypeOfDiagram);
            DiagForm.ShowDialog();
            
        }

        private void построитьToolStripMenuItem_Click(object sender, EventArgs e)
        {

        }

        private void круговаяToolStripMenuItem_Click(object sender, EventArgs e)
        {
            TypeOfDiagram = 2;
            DiagramForm DiagForm = new DiagramForm(TypeOfDiagram);
            DiagForm.ShowDialog();
        }

        private void menuStrip1_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {

        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }


        private void AddIncomeTypeBtn_Click(object sender, EventArgs e)
        {
            AddTypeForm AddF = new AddTypeForm();
            AddF.Show();
        }

        private void morePicker_ValueChanged(object sender, EventArgs e)
        {

        }

        private void lessPicker_ValueChanged(object sender, EventArgs e)
        {

        }

        private void выходToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void открытьToolStripMenuItem_Click(object sender, EventArgs e)
        {
            OpenFileDialog od = new OpenFileDialog();
            od.Filter = "файл базы данных SQLite(*.db3)|*.db3";
            od.ShowDialog();
          //  if od.
            //SQLiteDriver drv = new SQLiteDriver(od.FileName);
        }

        private void оПрограммеToolStripMenuItem_Click(object sender, EventArgs e)
        {
            AboutForm form1 = new AboutForm();
            form1.ShowDialog();            
        }


    }
}
